CREATE DATABASE School; 
USE School; 
CREATE TABLE Student (Student_id INTEGER AUTO_INCREMENT , 
name VARCHAR(255), 
level VARCHAR(255), 
PRIMARY KEY (Student_id ))  ENGINE=INNODB;
CREATE TABLE TimeSlot (TimeSlot_id INTEGER AUTO_INCREMENT , 
days VARCHAR(255), 
times VARCHAR(255), 
PRIMARY KEY (TimeSlot_id ))  ENGINE=INNODB;
CREATE TABLE Course (Course_id INTEGER AUTO_INCREMENT , 
dept VARCHAR(255), 
credits VARCHAR(255), 
level VARCHAR(255), 
name VARCHAR(255), 
UNIQUE (level, name), 
PRIMARY KEY (Course_id ))  ENGINE=INNODB;
CREATE TABLE StuxTime (StuxTime_id INTEGER AUTO_INCREMENT , 
Student_id INTEGER NOT NULL , 
TimeSlot_id INTEGER NOT NULL , 
FOREIGN KEY (Student_id ) REFERENCES Student (Student_id ) ON UPDATE CASCADE ON DELETE CASCADE , 
FOREIGN KEY (TimeSlot_id ) REFERENCES TimeSlot (TimeSlot_id ) ON UPDATE CASCADE ON DELETE CASCADE , 
PRIMARY KEY (StuxTime_id ))  ENGINE=INNODB;
CREATE TABLE Enrol (Enrol_id INTEGER AUTO_INCREMENT , 
Dateof VARCHAR(255), 
Student_id INTEGER NOT NULL , 
Course_id INTEGER NOT NULL , 
FOREIGN KEY (Student_id ) REFERENCES Student (Student_id ) ON UPDATE CASCADE ON DELETE CASCADE , 
FOREIGN KEY (Course_id ) REFERENCES Course (Course_id ) ON UPDATE CASCADE ON DELETE CASCADE , 
PRIMARY KEY (Enrol_id ))  ENGINE=INNODB;
CREATE TABLE Class (Class_id INTEGER AUTO_INCREMENT , 
offerNumber VARCHAR(255), 
TimeSlot_id INTEGER NOT NULL , 
Course_id INTEGER NOT NULL , 
FOREIGN KEY (TimeSlot_id ) REFERENCES TimeSlot (TimeSlot_id ) ON UPDATE CASCADE ON DELETE CASCADE , 
FOREIGN KEY (Course_id ) REFERENCES Course (Course_id ) ON UPDATE CASCADE ON DELETE CASCADE , 
PRIMARY KEY (Class_id ))  ENGINE=INNODB;
CREATE TABLE TimeTable (TimeTable_id INTEGER AUTO_INCREMENT , 
Enrol_id INTEGER NOT NULL , 
Class_id INTEGER NOT NULL , 
StuxTime_id INTEGER NOT NULL , 
FOREIGN KEY (Enrol_id ) REFERENCES Enrol (Enrol_id ) ON UPDATE CASCADE ON DELETE CASCADE , 
FOREIGN KEY (Class_id ) REFERENCES Class (Class_id ) ON UPDATE CASCADE ON DELETE CASCADE , 
FOREIGN KEY (StuxTime_id ) REFERENCES StuxTime (StuxTime_id ) ON UPDATE CASCADE ON DELETE CASCADE , 
UNIQUE (StuxTime_id ), 
PRIMARY KEY (TimeTable_id ))  ENGINE=INNODB;
CREATE TABLE Fail(ErrorGID int(10) unsigned NOT NULL auto_increment, Message VARCHAR(128) DEFAULT NULL, Created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (ErrorGID), UNIQUE KEY MessageIndex (Message)) ENGINE=MEMORY DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED;

INSERT INTO `student` VALUES (1,'John Smith','Third Year'),(2,'Jane Doe','Second Year'),(3,'Harvey Rabbit','Grad Student');
INSERT INTO `timeslot` VALUES (1,'Monday, Wednesday, Friday','12-1'),(2,'Tuesday, Thursday','9-11'),(3,'Thursday','2-5');
INSERT INTO `course` VALUES (1,'History','3','Third Year','Intro to History'),(2,'Math','3','First Year','Calculus'),(3,'Computer Science','6','Fourth Year','Database Systems');
INSERT INTO `class` VALUES (1,'A',1,1),(2,'B',2,1),(3,'A',3,2),(4,'A',2,3),(5,'B',1,3);
INSERT INTO `enrol` VALUES (1,'Jan 1',1,1),(2,'Jan 3',1,2),(3,'Jan 3',2,3),(4,'Jan 1',3,1),(5,'Jan 4',2,2);
INSERT INTO `stuxtime` VALUES (1,1,1),(2,1,2),(3,1,3),(4,2,1),(5,2,2),(6,2,3),(7,3,1),(8,3,2),(9,3,3);
INSERT INTO `timetable` VALUES (1,1,1,1),(2,4,1,7),(3,1,2,2),(4,4,2,8),(5,2,3,3),(6,5,3,6),(7,3,4,5),(8,3,5,4);

DELIMITER $$ 
CREATE PROCEDURE School_pullAInsTimeTable0(id INT) 
BEGIN 
DECLARE var0, var2 INT; 
SELECT Course.Course_id INTO var0 FROM TimeTable, Enrol, Course WHERE TimeTable.Enrol_id =Enrol.Enrol_id AND TimeTable.TimeTable_id =id AND Enrol.Course_id=Course.Course_id ; 
SELECT Course.Course_id INTO var2 FROM TimeTable, Class, Course WHERE TimeTable.Class_id =Class.Class_id AND TimeTable.TimeTable_id =id AND Class.Course_id=Course.Course_id ; 
IF NOT (var0 <=> var2) 
THEN call Fail('Invalid Entry into Pullback constraint'); 
END IF; 
IF ( (SELECT COUNT(*) FROM TimeTable WHERE Enrol_id = (SELECT Enrol_id  FROM TimeTable WHERE TimeTable_id =id) AND Class_id =(SELECT Class_id  FROM TimeTable WHERE TimeTable_id =id) ) > 1 ) 
THEN call fail('Duplicate entry in pullback table.'); 
END IF; 
END$$ 
CREATE PROCEDURE School_pullAInsEnrol0(id INT) 
BEGIN 
IF( (SELECT COUNT(Class.Class_id ) FROM Class, Enrol, Course  WHERE Enrol.Enrol_id = id AND Enrol.Course_id =Course.Course_id AND Class.Course_id =Course.Course_id ) != 0 )
 THEN 
INSERT INTO TimeTable (Enrol_id , Class_id ) SELECT Enrol.Enrol_id , Class.Class_id FROM Enrol, Course, Class WHERE  Enrol.Course_id=Course.Course_id AND Class.Course_id=Course.Course_id AND Enrol.Enrol_id =id ; 
END IF; 
END$$ 
CREATE PROCEDURE School_pullAInsClass0(id INT) 
BEGIN 
IF( (SELECT COUNT(Enrol.Enrol_id ) FROM Enrol, Class, Course  WHERE Class.Class_id = id AND Class.Course_id =Course.Course_id AND Enrol.Course_id =Course.Course_id ) != 0 )
 THEN 
INSERT INTO TimeTable (Enrol_id , Class_id ) SELECT Enrol.Enrol_id , Class.Class_id FROM Enrol, Course, Class WHERE  Enrol.Course_id=Course.Course_id AND Class.Course_id=Course.Course_id AND Class.Class_id =id ; 
END IF; 
END$$ 
CREATE PROCEDURE School_pullDelTimeTable0(id INT) 
BEGIN 
DELETE FROM Course USING Enrol, Course, Class , TimeTable 
WHERE TimeTable.TimeTable_id=id AND TimeTable.Enrol_id=Enrol.Enrol_id AND Enrol.Course_id=Course.Course_id AND TimeTable.Class_id=Class.Class_id AND Class.Course_id=Course.Course_id ; 
END$$ 
CREATE PROCEDURE School_prodDel1(id INT) 
BEGIN 
DELETE FROM Student USING StuxTime, Student WHERE 
StuxTime.StuxTime_id=id AND StuxTime.Student_id=Student.Student_id ; 
DELETE FROM TimeSlot USING StuxTime, TimeSlot WHERE 
StuxTime.StuxTime_id=id AND StuxTime.TimeSlot_id=TimeSlot.TimeSlot_id ; 
END$$ 
CREATE PROCEDURE School_prodInsStudent1(id INT) 
BEGIN 
DECLARE var INT; 
IF (( (SELECT COUNT(*) FROM TimeSlot) != 0) ) 
 THEN 
SET var = id; 
INSERT INTO StuxTime( Student_id , TimeSlot_id ) 
SELECT Student.Student_id , TimeSlot.TimeSlot_id FROM Student, TimeSlot WHERE 
Student.Student_id =var; 
END IF; 
END$$ 
CREATE PROCEDURE School_prodInsTimeSlot1(id INT) 
BEGIN 
DECLARE var INT; 
IF (( (SELECT COUNT(*) FROM Student) != 0) ) 
 THEN 
SET var = id; 
INSERT INTO StuxTime( Student_id , TimeSlot_id ) 
SELECT Student.Student_id , TimeSlot.TimeSlot_id FROM Student, TimeSlot WHERE 
TimeSlot.TimeSlot_id =var; 
END IF; 
END$$ 
CREATE PROCEDURE fail(_Message VARCHAR(128)) BEGIN INSERT INTO Fail (Message) VALUES (_Message); INSERT INTO Fail (Message) VALUES (_Message); END$$ 
DELIMITER ; 
DELIMITER $$ 
CREATE TRIGGER StudentAInsertTrig AFTER INSERT ON Student FOR EACH ROW BEGIN 
call School_prodInsStudent1(NEW.Student_id ); 
END$$ 
CREATE TRIGGER TimeTableAInsertTrig AFTER INSERT ON TimeTable FOR EACH ROW BEGIN 
call School_pullAInsTimeTable0(NEW.TimeTable_id ); 
END$$ 
CREATE TRIGGER TimeSlotAInsertTrig AFTER INSERT ON TimeSlot FOR EACH ROW BEGIN 
call School_prodInsTimeSlot1(NEW.TimeSlot_id ); 
END$$ 
CREATE TRIGGER ClassAInsertTrig AFTER INSERT ON Class FOR EACH ROW BEGIN 
call School_pullAInsClass0(NEW.Class_id ); 
END$$ 
CREATE TRIGGER EnrolAInsertTrig AFTER INSERT ON Enrol FOR EACH ROW BEGIN 
call School_pullAInsEnrol0(NEW.Enrol_id ); 
END$$ 
CREATE TRIGGER TimeTableBDeleteTrig BEFORE DELETE ON TimeTable FOR EACH ROW BEGIN 
call School_pullDelTimeTable0(OLD.TimeTable_id ); 
END$$ 
CREATE TRIGGER StuxTimeBDeleteTrig BEFORE DELETE ON StuxTime FOR EACH ROW BEGIN 
call School_prodDel1(OLD.StuxTime_id ); 
END$$ 
DELIMITER ; 
